package cn.edu.sjtu.literature.db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.edu.sjtu.literature.db.mapping.CategoryResultMappingCallback;
import cn.edu.sjtu.literature.db.mapping.LiteratureResultMappingCallback;
import cn.edu.sjtu.literature.db.mapping.ResultMappingCallback;
import cn.edu.sjtu.literature.db.mapping.UserResultMappingCallback;
import cn.edu.sjtu.literature.entity.Category;
import cn.edu.sjtu.literature.entity.Literature;
import cn.edu.sjtu.literature.entity.User;

public class DBUtil {
	
	private static final Log LOG = LogFactory.getLog(DBUtil.class);
	
	private static DataSource dataSource;
	
	static {
		dataSource = setupDataSource();
	}
	
	private static DataSource setupDataSource() {
		BasicDataSource ds = new BasicDataSource();
		Properties properties = new Properties();
		try {
			properties.load(DBUtil.class.getResourceAsStream("/jdbc.properties"));
		} catch (IOException e) {
			LOG.error(e.getMessage(), e);
		}
		ds.setDriverClassName(properties.getProperty("jdbc.driverClassName"));
		ds.setUrl(properties.getProperty("jdbc.url"));
		ds.setUsername(properties.getProperty("jdbc.username"));
		ds.setPassword(properties.getProperty("jdbc.password"));
		ds.setDefaultAutoCommit(true);
		ds.setMaxActive(50);
		ds.setMaxIdle(50);
		ds.setMaxOpenPreparedStatements(20);
		ds.setMaxWait(10);
		ds.setMinIdle(10);
		return ds;
	}
	
	private static void shutdownDataSource() throws SQLException {
		((BasicDataSource) dataSource).close();
	}
	
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	public static int executeUpdate(String sql) throws SQLException {
		int count = 0;
		Statement stmt = null;
		Connection conn = getConnection();
		try {
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
			return count;
		} finally {
			free(stmt, conn);
		}
	}
		
	public static <X> List<X> query(String sql, ResultMappingCallback<X> rmCallback) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		List<X> results = new ArrayList<X>();
		Connection conn = getConnection();
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				results.add(rmCallback.doResultMapping(rs));
			}
		} finally {
			free(rs, stmt, conn);
		}
		return results;
	}
	
	public static <X> X querySingle(String sql, ResultMappingCallback<X> rmCallback) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		X result = null;
		Connection conn = getConnection();
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = rmCallback.doResultMapping(rs);
			}
		} finally {
			free(rs, stmt, conn);
		}
		return result;
	}
	
	public static void free(Connection con) {
		if (con != null ) {
			try{
				con.close();
			} catch (SQLException ex) {
				LOG.error(ex.getMessage(), ex);
			}
		}
	}

	public static void free(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				LOG.error(ex.getMessage(), ex);
			}
		}
	}

	public static void free(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				LOG.error(ex.getMessage(), ex);
			}
		}
	}

	public static void free(Statement stmt, Connection con) {
		free(stmt);
		free(con);
	}

	public static void free(ResultSet rs, Statement stmt) {
		free(rs);
		free(stmt);
	}

	public static void free(ResultSet rs, Statement stmt, Connection con) {
		free(rs);
		free(stmt, con);
	}
	
	public static void main(String[] args) throws SQLException {
		try {
			DBUtil.getConnection();
			LOG.debug("Connection OK.");
			
			User user = DBUtil.querySingle("select * from t_user where id = 1", new UserResultMappingCallback());
			LOG.debug(ToStringBuilder.reflectionToString(user, ToStringStyle.MULTI_LINE_STYLE));
			
			Category category = DBUtil.querySingle("select * from t_category where id = 1", new CategoryResultMappingCallback());
			LOG.debug(ToStringBuilder.reflectionToString(category, ToStringStyle.MULTI_LINE_STYLE));
			
			List<Category> categories = DBUtil.query("select * from t_category order by id", new CategoryResultMappingCallback());
			LOG.debug(categories.size());
			
			Literature literature = DBUtil.querySingle("select l.*, c.id as categoryId, c.name as categoryName from t_literature l inner join t_category c on c.id = l.category_id where l.id = 1", new LiteratureResultMappingCallback());
			LOG.debug(ToStringBuilder.reflectionToString(literature, ToStringStyle.MULTI_LINE_STYLE));
			
		} catch (Exception ex) {
			LOG.error("Connection Failed.", ex);
		} finally {
			shutdownDataSource();
		}
	}

}
